﻿Imports System.Text
Imports System.IO

Public Class Admin
    Public Structure TemplateInfo
        Public TemplateName As String
        Public SubjectLine As String
        Public Body As String
    End Structure

    Public Structure TaskTemplateInfo
        Public TemplateId As Integer
        Public TemplateName As String
        Public TemplateType As String
        Public Message As String
        Public TemplateWorkingDays As Integer
    End Structure
    Public Enum ConsultantType
        CaseWorker = 1
        SalesCon = 2
        AccountManager = 3
        CaseCoordinator = 4
    End Enum

    Public Enum ConsultantStatus
        Unassigned = 0
        Active = 1
        Inactive = 2
        Unknown = 3
    End Enum
    Public Const PRODUCT_RETIRED = -1
    Public Const PRODUCT_NONE = -2
    Public Shared EMAILBODYSTRING As String
    Public Shared EMAILSUBJECTSTRING As String
    Public Shared EMAILBCCSTRING As String
    Public Shared EMAILFROMSTRING As String
    Public Shared EMAILTOSTRING As String
    'Public Structure ConsultantInfo
    '    Public ConId As Integer
    '    Public ConsultantName As String
    '    Public DDI As String
    '    Public DDIUK As String
    '    Public Fax As String
    '    Public JobTitle As String
    '    Public Accreditation As String
    '    Public Dislaimer As Integer
    '    Public Email As String
    '    Public DepartmentId As Integer
    '    Public SupervisorId As Integer
    '    Public OISCId As Integer
    '    Public DisclaimerId As Integer
    '    Public StatusId As Integer
    '    Public NTUser As String
    '    Public BranchID As Integer
    '    Public AccessLevel As Integer
    '    Public EmailDraftLoc As String
    'End Structure
    Public Structure TitleInfo
        Public TitleId As Integer
        Public Title As String
        Public Dender As String
    End Structure
    Public Enum EmailTemplateCategory
        Automation = 1
        ProductRelated = 0
    End Enum
    Public Enum MenuOptionList
        All = 0
        Menu_Case = 1
        Candidate = 2
        Company = 3
        CompanuContact = 4
        Partner = 5
        Finance = 6
    End Enum
    Public Enum ConsultantRole
        CSM = 1
        CC = 2

    End Enum
    Public Shared Function GetDestinationCountry(ByVal value As Long) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess()
        Try
            Return db.GetSQLDataTable("Select Country_ID, Country_Name from L_Country where Country_Destination = " & value)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GetConsultantInfo(ByVal ConId As Integer) As CommonDataFunctions.ConsultantInfo

        Dim SQL As New StringBuilder
        Dim myData As DataTable
        Dim Db As New Global_Visas_Data_Layer.DataAccess()

        GetConsultantInfo = Nothing

        With SQL
            .Append("Select con_name, Con_DDI, Con_DDIUK, Con_Fax, Con_Jobtitle, Con_Accreditation, Con_DisclaimerID,")
            .Append("con_email,con_department,con_supervisorid,con_oisc,con_disclaimerid,")
            .Append("con_status,con_ntusername, con_BranchID,Con_SecurityLevel,con_EmaildraftLoc ")
            .Append(" from L_Consultant where Con_ID = " & ConId)
        End With

        myData = Db.GetSQLDataTable(SQL.ToString)

        If Not IsNothing(myData) Then
            If myData.Rows.Count > 0 Then
                Dim myCon As New CommonDataFunctions.ConsultantInfo

                With myCon
                    .ConId = ConId
                    .ConsultantName = IIf(IsDBNull(myData.Rows(0).Item("con_name")), "", myData.Rows(0).Item("con_name"))
                    .DDI = IIf(IsDBNull(myData.Rows(0).Item("con_ddi")), "", myData.Rows(0).Item("con_ddi"))
                    .DDIUK = IIf(IsDBNull(myData.Rows(0).Item("con_ddiuk")), "", myData.Rows(0).Item("con_ddiuk"))
                    .Fax = IIf(IsDBNull(myData.Rows(0).Item("con_fax")), "", myData.Rows(0).Item("con_fax"))
                    .JobTitle = IIf(IsDBNull(myData.Rows(0).Item("con_jobtitle")), "", myData.Rows(0).Item("con_jobtitle"))
                    .Accreditation = IIf(IsDBNull(myData.Rows(0).Item("con_accreditation")), "", myData.Rows(0).Item("con_accreditation"))
                    .Dislaimer = myData.Rows(0).Item("con_disclaimerid")
                    .Email = IIf(IsDBNull(myData.Rows(0).Item("con_email")), "", myData.Rows(0).Item("con_email"))
                    .DepartmentId = myData.Rows(0).Item("con_department")
                    .SupervisorId = myData.Rows(0).Item("con_supervisorid")
                    .OISCId = myData.Rows(0).Item("con_oisc")
                    .DisclaimerId = myData.Rows(0).Item("con_disclaimerid")
                    .StatusId = myData.Rows(0).Item("con_status")
                    .NTUser = IIf(IsDBNull(myData.Rows(0).Item("con_ntusername")), "", myData.Rows(0).Item("con_ntusername"))
                    .BranchID = myData.Rows(0).Item("con_BranchID")
                    .AccessLevel = myData.Rows(0).Item("Con_SecurityLevel")

                    .EmailDraftLoc = myData.Rows(0).Item("con_EmaildraftLoc")
                End With

                Return myCon
            End If
        End If

    End Function
    Public Shared Function GetCurrentMenuLayout(ByVal conID As Long) As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append(" SELECT ISNULL(D_MenuLayout_1.MenuLayout_Name, 'Not Set') AS LO1, ISNULL(D_MenuLayout_2.MenuLayout_Name, 'Not Set') AS LO2, ")
                .Append(" ISNULL(D_MenuLayout_3.MenuLayout_Name, 'Not Set') AS LO3")
                .Append("  FROM D_MenuLayout D_MenuLayout_3 RIGHT OUTER JOIN")
                .Append("  D_MenuLayout D_MenuLayout_2 RIGHT OUTER JOIN")
                .Append("  L_Consultant LEFT OUTER JOIN")
                .Append("  D_MenuLayout D_MenuLayout_1 ON L_Consultant.Con_MenuLayout1 = D_MenuLayout_1.MenuLayout_ID ON")
                .Append(" D_MenuLayout_2.MenuLayout_ID = L_Consultant.Con_MenuLayout2 ON D_MenuLayout_3.MenuLayout_ID = L_Consultant.Con_MenuLayout3")
                .Append("  WHERE (L_Consultant.Con_ID = " & conID & ")")
            End With

            Return db.GetSQLDataTable(SQL.ToString)

        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function
    ' End Error handling block.
    Public Shared Function MenuLayoutItemsCombo() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append(" SELECT     TOP 100 PERCENT MenuOptionGroup_ID, MenuOptionGroup_Name")
                .Append(" FROM dbo.I_MenuOptionGroup")
                .Append(" ORDER BY MenuOptionGroup_ID")
            End With
            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function MenuLayoutForConsultant(ByVal conid As Long) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("SELECT MenuLayout_ID, MenuLayout_Name, Case MenuLayout_UserId when 0 then 'System' else'' end as Type ")
                .Append(" FROM D_MenuLayout WHERE ")
                .Append("(MenuLayout_UserID IN (" & conid & ", 0)) ORDER BY MenuLayout_Name")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function SetMenuLayout(ByVal MenuItemID As Integer, ByVal MenuItemOption As Integer, ByVal conid As Integer) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("update l_consultant set con_menulayout" & MenuItemID & "=" & MenuItemOption & " where con_id =  " & conid)
            End With
            Return db.ExecuteSQL(SQL.ToString)
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetMenuBookMarks(Optional ByVal MenuoptionNo As MenuOptionList = MenuOptionList.All) As DataTable
        Dim sql As New StringBuilder
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            sql = New StringBuilder

            With sql

                .Append("SELECT I_MenuOption.MenuOption_ID, I_MenuOption.MenuOption_Name, I_MenuOptionGroup.MenuOptionGroup_Name")
                .Append(" FROM I_MenuOption INNER JOIN")
                .Append(" I_MenuOptionGroup ON I_MenuOption.MenuOption_Group = I_MenuOptionGroup.MenuOptionGroup_ID")

                If MenuoptionNo <> MenuOptionList.All Then
                    .Append(" AND MenuOption_Group = " & MenuoptionNo)
                End If

                .Append(" ORDER BY I_MenuOptionGroup.MenuOptionGroup_ID, I_MenuOption.MenuOption_Name")
            End With

            Return db.GetSQLDataTable(sql.ToString)

        Catch ex As Exception
            Return Nothing
        Finally
            sql = Nothing
            db = Nothing
        End Try

    End Function
    Public Shared Function GetEmailTemplate(ByVal TemplateId As Integer) As TemplateInfo
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            Dim myData As DataTable
            Dim myInfo As New TemplateInfo

            With SQL
                .Append("Select EMailTemplate_Name, EMailTemplate_SubjectLine, EMailTemplate_Body from D_EMailTemplates")
                .Append(" Where EMailTemplate_ID = " & TemplateId)
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then

                    With myInfo
                        .TemplateName = myData.Rows(0).Item("emailtemplate_name")
                        .SubjectLine = myData.Rows(0).Item("emailtemplate_subjectline")
                        .Body = myData.Rows(0).Item("emailtemplate_body")
                    End With

                End If

            End If

            myData.Dispose()

            Return myInfo
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try

    End Function
    Public Shared Function GetSMSTemplate(ByVal TemplateId As Integer) As TemplateInfo
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            Dim myData As DataTable
            Dim myInfo As New TemplateInfo

            With SQL
                .Append("Select SMSTemplate_Name, SMSTemplate_Body from D_SMSTemplate")
                .Append(" Where SMSTemplate_id = " & TemplateId)
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then

                    With myInfo
                        .TemplateName = myData.Rows(0).Item("SMSTemplate_Name")
                        .Body = myData.Rows(0).Item("SMSTemplate_Body")
                    End With

                End If

            End If

            myData.Dispose()

            Return myInfo
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try

    End Function
    Public Shared Function BookmarkList() As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("SELECT Bookmark_ID, Bookmark_Text, Bookmark_Desc, Bookmark_Legacy")
                .Append(" FROM I_SL_Bookmarks")
                .Append(" WHERE (Bookmark_Legacy = 0)")
                .Append(" ORDER BY Bookmark_Text")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function EmailTemplateList(ByVal Category As EmailTemplateCategory) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            With SQL

                .Append(" SELECT     TOP 100 PERCENT dbo.D_EmailTemplates.EMailTemplate_ID, MAX(dbo.D_EmailTemplates.EMailTemplate_Name) AS Title, ")
                .Append(" COUNT(dbo.I_SL_SLSMailTemplates.SLSMailTemplate_ID) AS Uses")
                .Append(" FROM         dbo.D_EmailTemplates LEFT OUTER JOIN")
                .Append(" dbo.I_SL_SLSMailTemplates ON dbo.D_EmailTemplates.EMailTemplate_ID = dbo.I_SL_SLSMailTemplates.SLSMailTemplate_TemplateID")
                .Append(" GROUP BY dbo.D_EmailTemplates.EMailTemplate_ID, dbo.D_EmailTemplates.EMailTemplate_Category")
                .Append(" HAVING(dbo.D_EmailTemplates.EMailTemplate_Category = ")
                If Category = EmailTemplateCategory.Automation Then
                    .Append("1")
                ElseIf Category = EmailTemplateCategory.ProductRelated Then
                    .Append("0")
                End If
                .Append(" ) ORDER BY MAX(dbo.D_EmailTemplates.EMailTemplate_Name)")

            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function SMSTemplateList(ByVal branchid As Integer) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL

                .Append("SELECT     SMSTemplate_id, SMSTemplate_Name, SMSTemplate_BranchID")
                .Append(" FROM dbo.d_SMSTemplate")
                .Append(" WHERE     SMSTemplate_BranchID = " & branchid)

            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function UpdateEmailTemplateItem(ByVal SQL As String) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            If Not db.ExecuteSQL(SQL.ToString) Then
                Return False
            End If

            Return True
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function DeleteEmailTemplate(ByVal TemplateId As Integer, ByVal EventMessage As String) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Try

            If Not db.ExecuteSQL("Delete from I_SL_SLSMailTemplates where SLSMailTemplate_TemplateID = " & TemplateId) Then
                Exit Function
            End If

            If Not db.ExecuteSQL("update D_EmailTemplates set emailtemplates_status = 1 Where EMailTemplate_ID = " & TemplateId) Then
                Exit Function
            End If

            tsk.GblFunc_Add_SysEvent("Admin (SL)", EventMessage)
            Return True
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            tsk = Nothing
        End Try
    End Function
    Public Shared Function AddEmailTemplate(ByVal Title As String, ByVal Subject As String, ByVal Body As String, ByVal EnteredBy As String) As Long

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Dim tsk As New TasksEvents
        Dim cmn As New CommonFuncs
        Try

            SQL = New StringBuilder
            Dim TemplateId As Long

            With SQL
                .Append("Insert Into D_EMailTemplates ")
                .Append(" (EMailTemplate_Name, EMailTemplate_SubjectLine, EmailTemplate_Body, EMailTemplate_EnteredBy)")
                .Append(" VALUES(")
                .Append(" '" & cmn.SwapQuotes(Title) & "',")
                .Append(" '" & cmn.SwapQuotes(Subject) & "',")
                .Append(" '" & cmn.SwapQuotes(Body) & "',")
                .Append(" '" & cmn.SwapQuotes(EnteredBy) & ")")
            End With
            TemplateId = db.ExecuteSQL(SQL.ToString, True)
            If TemplateId > 0 Then
                Dim Msg As New StringBuilder

                With Msg
                    .Append("New EMail Template created, No. " & TemplateId & " , details below")
                    .Append(vbCrLf & vbCrLf & "Title: " & Title)
                    .Append(vbCrLf & "Subject: " & Subject)
                    .Append(vbCrLf & "Body: " & vbCrLf & Body)
                End With

                tsk.GblFunc_Add_SysEvent("Admin (SL)", Msg.ToString)

                Return TemplateId

            Else
                Return 0
                Exit Function
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
            tsk = Nothing
            cmn = Nothing
        End Try

    End Function
    Public Shared Function AddSMSTemplate(ByVal Title As String, ByVal Subject As String, ByVal Body As String, ByVal EnteredBy As String, ByVal BranchID As Long) As Long

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Dim tsk As New TasksEvents
        Dim cmn As New CommonFuncs
        Dim Msg As StringBuilder
        Try

            SQL = New StringBuilder
            Dim TemplateId As Long

            With SQL
                .Append("Insert Into D_SMSTemplate ")
                .Append(" (SMSTemplate_Name, SMSTemplate_Body, SMSTemplate_Enteredby, SMSTemplate_BranchID)")
                .Append(" VALUES(")
                .Append(" '" & cmn.SwapQuotes(Title) & "',")
                .Append(" '" & cmn.SwapQuotes(Body) & "',")
                .Append(" '" & cmn.SwapQuotes(EnteredBy) & "',")
                .Append(BranchID & ")")
            End With
            TemplateId = db.ExecuteSQL(SQL.ToString, True)
            If TemplateId > 0 Then
                'TemplateId = db.GetIdentity


                Msg = New StringBuilder
                With Msg
                    .Append("New SMS Template created, No. " & TemplateId & " , details below")
                    .Append(vbCrLf & vbCrLf & "Title: " & Title)
                    .Append(vbCrLf & "Subject: " & Subject)
                    .Append(vbCrLf & "Body: " & vbCrLf & Body)
                End With

                tsk.GblFunc_Add_SysEvent("Admin (SL)", Msg.ToString)

                Return TemplateId

            Else
                Return 0
                Exit Function
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
            tsk = Nothing
            cmn = Nothing
            Msg = Nothing
        End Try

    End Function
    Public Shared Function MenuLayoutColumns(ByVal MenuLayoutid As Long) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append(" SELECT I_MenuOption.MenuOption_ID, I_MenuOption.MenuOption_Name, D_MenuLink.MenuLink_Column ")
                .Append(" FROM I_MenuOption RIGHT OUTER JOIN D_MenuLink ON I_MenuOption.MenuOption_ID = D_MenuLink.MenuLink_ItemID ")
                .Append(" WHERE (D_MenuLink.MenuLink_LayoutID = " & MenuLayoutid & ") ORDER BY D_MenuLink.MenuLink_Column")
            End With
            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function EmailTemplateIsDuplicate(ByVal Title As String) As Integer
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New Global_Visas_Business_Layer.CommonFuncs
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append(" Select count(*) from D_EMailTemplates where EMailTemplate_Name ='")
                .Append(cmn.SwapQuotes(Title) & "'")
            End With

            Return db.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return 1
        Finally
            SQL = Nothing
            db = Nothing
            cmn = Nothing
        End Try
    End Function
    Public Shared Function AddNewLayout(ByVal layoutid As Integer, ByVal ArrMenu() As String, Optional ByVal layoutname As String = "") As Boolean


        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New Global_Visas_Business_Layer.CommonFuncs
        Dim SQL As StringBuilder
        Dim blnReturn As Boolean = False
        Try
            db.BeginTrans()
            If layoutid = 0 Then
                'add new one
                If layoutname = String.Empty Then
                    Return False
                End If
                SQL = New StringBuilder
                With SQL
                    .Append("Insert into D_MenuLayout (MenuLayout_Name, MenuLayout_CreatedBy, MenuLayout_UserID, MenuLayout_DTCreated)")
                    .Append(" VALUES('" & cmn.SwapQuotes(layoutname) & "', '" & GlobalCRM.CurrentConName & "'," & GlobalCRM.CurrentConID & ", '" & cmn.FormatDate(CommonDataFunctions.GetLocalServer()) & "')")
                End With
                layoutid = db.ExecuteSQL(SQL.ToString, True)
                If layoutid > 0 Then
                    'layoutid = db.GetIdentity()
                Else
                    db.RollbackTrans()
                    Return False
                End If
                For i = 2 To 8

                    SQL = New StringBuilder

                    With SQL
                        .Append("  Insert into D_MenuLink (Menulink_LayoutID, MenuLink_Column, MenuLink_ItemID)")
                        .Append("values(" & layoutid & "," & i & "," & ArrMenu(i - 2) & ")")
                    End With

                    If Not db.ExecuteSQL(SQL.ToString) Then
                        db.RollbackTrans()
                        blnReturn = False
                        Exit For
                    Else
                        blnReturn = True
                    End If
                Next
                If blnReturn = True Then
                    db.CommitTrans()
                End If

            Else
                'Update the existing ones.
                For i = 2 To 8
                    db.GetSQLScalar(" Select MenuLink_ItemID from D_MenuLink where MenuLink_LayoutID = " & layoutid & "and MenuLink_column = " & i)
                    SQL = New StringBuilder

                    With SQL
                        '.Append("Update D_MenuLink set MenuLink_ItemID = " & ArrMenu(i - 2) & " where Menulink_ID = ")
                    End With

                    If Not db.ExecuteSQL(SQL.ToString) Then
                        db.RollbackTrans()
                        blnReturn = False
                        Exit For
                    Else
                        blnReturn = True
                    End If
                Next
                If blnReturn = True Then
                    db.CommitTrans()
                End If
            End If

            Return blnReturn
        Catch ex As Exception
            Return False
        Finally
            SQL = Nothing
            db = Nothing
            cmn = Nothing
        End Try

    End Function
    Public Shared Function ForceEnableOnlineLoginArea(ByVal caseid As Long, ByVal EnableorDisable As Integer) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            If EnableorDisable = 1 Then
                If DB.ExecuteSQL("update d_case set case_forceonlinelogin = 1 where case_id = " & caseid) = True Then
                    Return True
                Else
                    Return False
                End If
            ElseIf EnableorDisable = 2 Then
                If DB.ExecuteSQL("update d_case set case_forceonlinelogin = 0 where case_id = " & caseid) = True Then
                    Return True
                Else
                    Return False
                End If
            End If
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Shared Function ForceEnableOnlinePayment(ByVal caseid As Long, ByVal EnableorDisable As Integer) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            If EnableorDisable = 1 Then
                If DB.ExecuteSQL("update d_case set case_forceonlinepayment = 1 where case_id = " & caseid) = True Then
                    Return True
                Else
                    Return False
                End If
            ElseIf EnableorDisable = 2 Then
                If DB.ExecuteSQL("update d_case set case_forceonlinepayment = 0 where case_id = " & caseid) = True Then
                    Return True
                Else
                    Return False
                End If
            End If

        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Shared Function ConsultantList(Optional ByVal ActiveOnly As Boolean = True) As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder

        With SQL

            .Append("SELECT L_Consultant.Con_ID as 'Con Id', L_Consultant.con_name as Name, L_Consultant.Con_JobTitle as 'Job Title', isnull(L_Departments.Department_Name, '') AS Dept,")
            .Append(" CASE L_Consultant.Con_Status WHEN 1 THEN '' ELSE 'Inactive' END AS Status")
            .Append(" FROM L_Consultant LEFT OUTER JOIN")
            .Append(" L_Departments ON L_Consultant.Con_Department = L_Departments.Department_ID")

            If ActiveOnly Then
                .Append(" Where Con_Status = 1")
            End If
            .Append(" ORDER BY con_name")
        End With
        Dim returnConsultantList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        'For Each currentColumn As DataColumn In returnConsultantList.Columns
        '    If currentColumn.ColumnName = "Con_ID" Then
        '        currentColumn.ColumnName = "Con Id"
        '    ElseIf currentColumn.ColumnName = "con_name" Then
        '        currentColumn.ColumnName = "Name"
        '    ElseIf currentColumn.ColumnName = "Con_JobTitle" Then
        '        currentColumn.ColumnName = "Job Title"
        '    End If
        'Next

        Return returnConsultantList


    End Function

    Public Shared Function TitleList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder

        With SQL
            .Append("select Title_ID as Id ,Title_Text as Title,")
            .Append("  Case Title_Gender when 0 then 'Either' when 1 then 'Male' when 2 then 'Female' end as Gender")
            .Append(" from l_titles")
        End With
        Dim returnTitleList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnTitleList
    End Function


    Public Shared Function ChargesInGroupList(ByVal ChargeGroupId As Integer) As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder
        With SQL
            .Append(" select A_ChargeLinks.ChargeLink_ID,A_ChargeTypes.ChargeType_ID,A_ChargeTypes.ChargeType_Name as 'Charges In Group'")
            .Append("  from A_ChargeLinks join A_ChargeTypes on ")
            .Append("  A_ChargeLinks.ChargeLink_ChargeTypeID = A_ChargeTypes.ChargeType_ID ")
            .Append(" where A_ChargeLinks.ChargeLink_ChargeGroupID=" & ChargeGroupId)
            .Append(" Order By A_ChargeTypes.ChargeType_Name")
        End With
        Dim returnChargesInGroupList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnChargesInGroupList
    End Function


    Public Shared Function EventsInGroupList(ByVal EventGroupId As Integer) As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder
        With SQL
            .Append("SELECT I_EventLinks.EventLink_ID, L_EventTypes.EventType_Name as 'Events In Group', ")
            .Append(" ISNULL(Sec_Access.SecAccess_Name, '') AS SecAccess,")
            .Append(" CASE EventLink_StageStart when 0 then 'New Lead' when 100 then 'Case Initiated' when 1000 then 'File Closed' else L_CaseStages_Start.CaseStage_Title end as 'From',")
            .Append(" CASE EventLink_StageEnd when 0 then 'After Closed' when 999 then 'File Closed' else L_CaseStages_Start.CaseStage_Title end as Till ")
            .Append(" FROM I_EventLinks INNER JOIN")
            .Append(" L_EventTypes ON I_EventLinks.EventLink_EventTypeID = L_EventTypes.EventType_ID LEFT OUTER JOIN")
            .Append(" Sec_Access ON I_EventLinks.EventLink_SecAccess = Sec_Access.SecAccess_ID")
            .Append(" LEFT OUTER JOIN")
            .Append(" L_CaseStages L_CaseStages_Start ON I_EventLinks.EventLink_StageStart = L_CaseStages_Start.CaseStage_Number LEFT OUTER JOIN")
            .Append(" L_CaseStages L_CaseStages_End ON I_EventLinks.EventLink_StageEnd = L_CaseStages_End.CaseStage_Number")
            .Append(" WHERE I_EventLinks.EventLink_EventGroupID = " & EventGroupId)
            .Append(" ORDER By EventLink_Order")
        End With
        Dim returnEventsInGroupList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnEventsInGroupList
    End Function

    Public Shared Function ChargesTypesList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder
        With SQL
            .Append("select A_chargeTypes.ChargeType_ID,A_chargeTypes.ChargeType_Name as 'All Charge Types',")
            .Append(" A_chargeTypes.ChargeType_VAT as Tax,  A_ChargeCategories.ChargeCat_Name as Category ,A_ChargeCategories.ChargeCat_ID ")
            .Append(" from A_chargeTypes left outer join A_ChargeCategories on ")
            .Append(" A_chargeTypes.ChargeType_CategoryID = A_ChargeCategories.ChargeCat_ID")
            .Append(" order by A_chargeTypes.ChargeType_Name")
        End With
        Dim returnChargeTypesList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        For Each currentRow As DataRow In returnChargeTypesList.Rows
            If currentRow(3) Is Nothing Then
                currentRow(3) = String.Empty
            End If
        Next
        Return returnChargeTypesList
    End Function
    Public Shared Function PaymentTypesList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder
        With SQL
            .Append("SELECT [PaymentType_ID],[PaymentType_Name]FROM[Global].[dbo].[A_PaymentTypes]")
        End With
        Dim returnPaymentTypesList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        For Each currentRow As DataRow In returnPaymentTypesList.Rows
            'If currentRow(3) Is Nothing Then
            '    currentRow(3) = String.Empty
            'End If
        Next
        Return returnPaymentTypesList
    End Function

    Public Shared Function PaymentMethodsList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder
        With SQL
            .Append("SELECT [PaymentMethod_ID],[PaymentMethod_Name] FROM [Global].[dbo].[A_PaymentMethods]")
        End With
        Dim returnPaymentMethodsList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        For Each currentRow As DataRow In returnPaymentMethodsList.Rows
            'If currentRow(3) Is Nothing Then
            '    currentRow(3) = String.Empty
            'End If
        Next
        Return returnPaymentMethodsList
    End Function


    Public Shared Function CategoryList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder
        With SQL
            .Append("select ChargeCat_ID,ChargeCat_Name,ChargeCat_GovtFee ")
            .Append(" from A_ChargeCategories where ChargeCat_Visible=1")
        End With
        Dim returnCategoryList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnCategoryList
    End Function



    Public Shared Function ChargeGroupsList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder

        With SQL
            .Append("select ChargeGroup_ID,ChargeGroup_Name as 'Charge Groups' from A_ChargeGroups Order By ChargeGroup_Name")

        End With
        Dim returnChargeGroupsList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnChargeGroupsList
    End Function

    Public Shared Function EventGroupsList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder
        With SQL
            .Append("Select EventGroup_ID, EventGroup_Name as 'Event Groups' FROM I_EventGroups Order By EventGroup_Name")

        End With
        Dim returnEventGroupsList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnEventGroupsList
    End Function

    Public Shared Function EventTypesList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder
        With SQL
            .Append("Select EventType_ID, EventType_Name as 'All Event Types', ")
            .Append(" Case EventType_Scope When 1 then 'Sales' When 2 then 'Casework' else 'All' end as Users,")
            .Append(" Case EventType_CustomerType When 0 then 'All' when 1 then 'Private' when 2 then 'Corporate' end As Customer")
            .Append(" FROM L_EventTypes")
            .Append(" Order By EventType_Name")
        End With
        Dim returnEventTypesList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnEventTypesList
    End Function

    Public Shared Function QualificationList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder

        With SQL
            .Append("select Qual_ID ,Qual_Name as Qualification,case  Qual_Degree ")
            .Append("  when 0 then 'No'  when 1 then 'Yes' End as 'Degree Equivalent',Qual_Country from l_qualifications  order by Qual_Name")
        End With
        Dim returnQualificationList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnQualificationList
    End Function

    'Public Shared Function WhereHearList() As DataTable
    '    Dim DB As New Global_Visas_Data_Layer.DataAccess()
    '    Dim SQL As New StringBuilder

    '    With SQL
    '        .Append("select WhereHear_ID,WhereHear_Text as 'Where Hear',Case WhereHear_Website when 1 then 'Yes' when 0 then 'No' end as 'Visible On Website' from l_wherehear")
    '    End With
    '    Dim returnWhereHearList As DataTable = DB.GetSQLDataTable(SQL.ToString)
    '    Return returnWhereHearList
    'End Function

    Public Shared Function UKVisaList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder

        With SQL
            .Append("select UKVisa_ID,UKVisa_Name as 'UK Visa',case UkVisa_Indefinite when 1 then 'Yes' when 0 then '' end as Indefinite,UKVisa_Order from l_ukvisa")
        End With
        Dim returnUKVisaList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnUKVisaList
    End Function

    Public Shared Function CountryList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder

        With SQL
            .Append("select Country_ID ,Country_Name as Country,Country_Nationality as Nationality,")
            .Append("Country_EU as EU,Country_Migration as Migration from l_country ")
        End With
        Dim returnUKVisaList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnUKVisaList
    End Function

    Public Shared Function CountyList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder

        With SQL
            .Append("select County_ID ,County_Name as County from l_county")
        End With
        Dim returnUKVisaList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnUKVisaList
    End Function

    Public Shared Function IndustryList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder

        With SQL
            .Append("select Industry_ID ,Industry_Name as Industry from l_industry order by Industry_Name")
        End With
        Dim returnIndustryList As DataTable = DB.GetSQLDataTable(SQL.ToString)
        Return returnIndustryList
    End Function

    Public Shared Function DepartmentList(Optional ByVal intBranchID As Integer = -1) As DataTable
        ' get list of departments
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder

        With SQL
            If intBranchID = -1 Then
                Return DB.GetSQLDataTable("Select Department_ID, Department_Name from L_Departments order by Department_name")
            Else
                Return DB.GetSQLDataTable("Select Department_ID, Department_Name from L_Departments where Department_BranchID = " & CStr(intBranchID) & "  order by Department_name ")
            End If
        End With

    End Function
    Public Shared Function DisclaimerList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("select disclaimer_id,disclaimer_title from l_disclaimer")
    End Function
    Public Shared Function OISCList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("select oisc_id,oisc_description from l_oisc")
    End Function
    Public Shared Function BranchList() As DataTable
        Dim Db As New Global_Visas_Data_Layer.DataAccess()
        Return Db.GetSQLDataTable("Select Branch_ID, Branch_Name from l_Branch")
    End Function
    Public Shared Function SecurityList() As DataTable
        Dim Db As New Global_Visas_Data_Layer.DataAccess()
        Return Db.GetSQLDataTable("Select Security_ID, Security_Name from l_Security")
    End Function
    Public Shared Function GetCasesFromConsultant(ByVal conid As Long, ByVal role As ConsultantRole) As DataTable
        Dim Db As New Global_Visas_Data_Layer.DataAccess()
        Dim sql As StringBuilder
        Try
            sql = New StringBuilder
            With sql
                .Append(" SELECT   dbo.D_Case.Case_ID as caseid,  dbo.L_ProductTypes.ProductType_Name as Prodtype,  dbo.L_Products.Product_Name as prodname")
                .Append(" FROM         dbo.D_Case INNER JOIN")
                .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
                .Append("                       dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID")
                If role = ConsultantRole.CSM Then
                    .Append(" WHERE     (dbo.D_Case.Case_ConCWID = " & conid & ") ")
                ElseIf role = ConsultantRole.CC Then
                    .Append(" WHERE     (dbo.D_Case.case_coordinatorid = " & conid & ") ")
                End If
                .Append(" AND (dbo.D_Case.Case_StatusID BETWEEN 100 AND 999) order by caseid")
            End With
            Return Db.GetSQLDataTable(sql.ToString)
        Catch ex As Exception

            Return Nothing
        Finally
            sql = Nothing
            Db = Nothing
        End Try
    End Function
    Public Shared Function GetUnassignedProductTypes(ByVal ConType As ConsultantType, ByVal ConStatus As ConsultantStatus, ByVal ConId As Integer, Optional ByVal Branchid As Integer = 0) As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try




            Dim ConColName As String = String.Empty
            Dim ConColName2 As String = String.Empty
            Dim ExtraCaseSQL As String = String.Empty
            Dim ExtraConSQL As String = String.Empty
            SQL = New StringBuilder
            With SQL

                .Append("SELECT distinct L_PRODUCTTYPES.PRODUCTTYPE_ID, L_PRODUCTTYPES.PRODUCTTYPE_NAME ")
                .Append(" FROM d_person per1 left outer join l_country ctry1 on per1.person_natid = ctry1.country_id,")
                .Append(" D_Case LEFT OUTER JOIN")
                .Append(" L_CaseStages ON D_Case.Case_StatusID = L_CaseStages.CaseStage_Number LEFT OUTER JOIN")
                .Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN")
                .Append(" L_ProductTypes ON L_Products.Product_Type = L_ProductTypes.ProductType_ID LEFT OUTER JOIN")
                .Append(" D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID LEFT OUTER JOIN")
                .Append(" L_Country ctry2 ON D_Person.Person_ResidentID = ctry2.Country_ID LEFT OUTER JOIN")
                .Append(" L_Country ctry3 ON D_Person.Person_DestinationID = ctry3.Country_ID LEFT OUTER JOIN")
                .Append(" L_Consultant L_Consultant_Sales ON D_Case.Case_ConSaleID = L_Consultant_Sales.Con_ID LEFT OUTER JOIN")
                .Append(" L_Consultant L_Consultant_CW ON D_Case.Case_ConCWID = L_Consultant_CW.Con_ID LEFT OUTER JOIN")
                .Append(" L_Consultant L_Consultant_CC ON D_Case.Case_CoordinatorID = L_Consultant_CC.Con_ID LEFT OUTER JOIN")
                .Append(" D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID")
                .Append(" WHERE ")
                .Append(" d_person.person_id = per1.person_id and")
                .Append(" (D_Case.Case_CompanyID <> 3065) AND (Case_EnteredBy <> 'WebTest') AND (D_Case.Case_CandidateID <> 100008)")

                Select Case ConType
                    Case ConsultantType.CaseWorker
                        ConColName = "ConCWID"
                        ConColName2 = "CW"
                        .Append(" AND  (D_Case.Case_StatusID BETWEEN 100 AND 999) ")
                        ExtraConSQL = " AND ((D_Case.Case_" & ConColName & " IN (0, 11)) OR (L_Consultant_CW.con_name is null)"
                    Case ConsultantType.SalesCon
                        If ConStatus = ConsultantStatus.Unknown Then
                            .Append(" AND  (D_Case.Case_StatusID < 100) ")
                        Else
                            .Append(" AND  (D_Case.Case_StatusID < 1000) ")
                        End If
                        ConColName = "ConSaleID"
                        ConColName2 = "Sales"
                        ExtraConSQL = "AND ((D_Case.Case_" & ConColName & " IN (0, 11)) OR (L_Consultant_Sales.con_name is null)"
                    Case ConsultantType.CaseCoordinator
                        ConColName = "CoordinatorID"
                        ConColName2 = String.Empty
                        .Append(" AND  (D_Case.Case_StatusID BETWEEN 100 AND 999) ")
                        ExtraConSQL = " AND ((D_Case.Case_" & ConColName & " IN (0, 11)) OR (L_Consultant_CW.con_name is null)"
                End Select


                If ConId = 0 Then
                    'ExtraConSQL = ExtraConSQL
                ElseIf ConId > 0 Then
                    'MJG 03/08/06 Added extra bracket here as was crapping out
                    If ConId = 0 Then
                        ExtraConSQL = " AND (D_case.case_" & ConColName & " = " & ConId & ")"
                    Else
                        ExtraConSQL = " AND (D_case.case_" & ConColName & " = " & ConId & ")"
                    End If
                End If


                .Append(ExtraCaseSQL)
                .Append(ExtraConSQL)

                If ConStatus = ConsultantStatus.Unassigned And ConId = 0 Then
                    .Append(")")
                End If


                .Append(" ORDER BY  L_PRODUCTTYPES.PRODUCTTYPE_ID, L_PRODUCTTYPES.PRODUCTTYPE_NAME ")

                Return DB.GetSQLDataTable(.ToString)

            End With

        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing

        End Try

    End Function
    Public Shared Function GetUnassignedCases(ByVal ConType As ConsultantType, ByVal ConStatus As ConsultantStatus, ByVal DepartmentId As Integer, ByVal ConId As Integer, ByVal ProductTypeID As Integer) As DataTable


        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            Dim ConColName As String = String.Empty
            Dim ConColName2 As String = String.Empty
            Dim ExtraCaseSQL As String = String.Empty
            Dim ExtraConSQL As String = String.Empty
            SQL = New StringBuilder
            With SQL

                .Append("SELECT distinct dbo.l_GVScore.gvscore_name as [Score],D_Case.Case_ID, D_Case.Case_DTSold, ")
                .Append(" isnull(D_Company.Company_Name,'Private') as Company, ")
                .Append(" isnull(D_Person.Person_FName + ' ' + D_Person.Person_LName,'Unspecified') as Candidate, ")
                .Append("D_PhoneNumber.PhoneNumber_Number as PhoneNumber, D_EmailAddress.EMailAddress_Address as Email,D_Person.Person_Jobtitle as Occupation,")
                .Append(" isnull(L_Products.Product_Name,'Unspecified') as Product, ")
                .Append(" isnull(L_CaseStages.CaseStage_Title,'NO STATUS') as Status , ")
                .Append(" isnull(L_Consultant_Sales.con_name,'Unassigned') as SalesCon,")
                .Append(" isnull(L_Consultant_CW.con_name,'Unassigned') as Caseworker,")
                .Append(" isnull(L_Consultant_CC.con_name,'Unassigned') as Coordinator,")
                .Append(" D_case.Case_DTentered as 'Date Created',")
                .Append(" ctry1.country_nationality as Nationality,")
                .Append(" ctry2.Country_Name as Residence,")
                .Append(" ctry3.Country_Name as Destination,")
                .Append(" D_case.Case_enteredBy as 'Entered By',")
                .Append(" D_Case.Case_MainCaseID as 'Linked Case' ,D_Case.Case_ID,D_Person.Person_id as candidateid,D_Case.Case_StatusID as CaseStatusID, L_Consultant_Sales.Con_id as SaleConID")
                .Append(" FROM d_person per1 left outer join l_country ctry1 on per1.person_natid = ctry1.country_id,")
                .Append(" D_Case LEFT OUTER JOIN")
                .Append(" L_CaseStages ON D_Case.Case_StatusID = L_CaseStages.CaseStage_Number LEFT OUTER JOIN")
                .Append(" l_GVScore ON D_Case.case_GVScore = l_GVScore.gvscore_id LEFT OUTER JOIN")
                .Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN")
                .Append(" L_ProductTypes ON L_Products.Product_Type = L_ProductTypes.ProductType_ID LEFT OUTER JOIN")
                .Append(" D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID LEFT OUTER JOIN")
                .Append(" L_Country ctry2 ON D_Person.Person_ResidentID = ctry2.Country_ID LEFT OUTER JOIN")
                .Append(" L_Country ctry3 ON D_Person.Person_DestinationID = ctry3.Country_ID LEFT OUTER JOIN")
                .Append(" L_Consultant L_Consultant_Sales ON D_Case.Case_ConSaleID = L_Consultant_Sales.Con_ID LEFT OUTER JOIN")
                .Append(" L_Consultant L_Consultant_CW ON D_Case.Case_ConCWID = L_Consultant_CW.Con_ID LEFT OUTER JOIN")
                .Append(" L_Consultant L_Consultant_CC ON D_Case.Case_CoordinatorID = L_Consultant_CC.Con_ID LEFT OUTER JOIN")
                .Append(" D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID LEFT OUTER JOIN  ")
                .Append(" D_PhoneNumber ON D_Person.Person_ID=D_PhoneNumber.PhoneNumber_PersonID LEFT OUTER JOIN ")
                .Append(" D_EmailAddress ON D_Person.Person_ID=D_EmailAddress.EMailAddress_PersonID ")
                .Append(" WHERE ")
                .Append(" d_person.person_id = per1.person_id and (Case_DIYCase = 0 or Case_DIYCase is null) and")
                .Append(" (D_Case.Case_CompanyID <> 3065) AND (Case_EnteredBy <> 'WebTest') AND (D_Case.Case_CandidateID <> 100008)")

                Select Case ConType
                    Case ConsultantType.CaseWorker
                        ConColName = "ConCWID"
                        ConColName2 = "CW"
                        .Append(" AND  (D_Case.Case_StatusID BETWEEN 0 AND 16) ")
                        ExtraConSQL = " AND ((D_Case.Case_" & ConColName & " IN (0, 11)) OR (L_Consultant_CW.con_name is null)"
                    Case ConsultantType.SalesCon
                        If ConStatus = ConsultantStatus.Unknown Then
                            .Append(" AND  (D_Case.Case_StatusID BETWEEN 0 AND 16)")
                        ElseIf ConStatus = ConsultantStatus.Unassigned And ConId = 0 Then
                            ' for new web leads only
                            .Append(" AND (D_Case.Case_StatusID = 1 )")
                        Else
                            .Append(" AND  (D_Case.Case_StatusID BETWEEN 0 AND 16) ")
                        End If
                        ConColName = "ConSaleID"
                        ConColName2 = "Sales"
                        ExtraConSQL = "AND ((D_Case.Case_" & ConColName & " IN (0, 11)) OR (L_Consultant_Sales.con_name is null)"
                    Case ConsultantType.CaseCoordinator
                        ConColName = "CoordinatorID"
                        ConColName2 = String.Empty
                        .Append(" AND  (D_Case.Case_StatusID BETWEEN 0 AND 16) ")
                        ExtraConSQL = " AND ((D_Case.Case_" & ConColName & " IN (0, 11)) OR (L_Consultant_CW.con_name is null)"
                End Select

                'Select Case ConStatus
                '    Case ConsultantStatus.Unassigned
                '        'ExtraConSQL = ExtraConSQL
                '    Case ConsultantStatus.Active
                '        ExtraConSQL = " and  (L_Consultant_" & ConColName2 & ".Con_Status > 0)"
                '    Case ConsultantStatus.Inactive
                '        ExtraConSQL = " and  (L_Consultant_" & ConColName2 & ".Con_Status = 0)"
                '    Case Else
                '        ExtraConSQL = ExtraConSQL ' do nothing
                'End Select

                If ConId = 0 Then
                    ExtraConSQL = " AND (D_case.case_" & ConColName & " = " & ConId & ")"
                ElseIf ConId > 0 Then
                    'MJG 03/08/06 Added extra bracket here as was crapping out
                    If ConId = 0 Then
                        ExtraConSQL = " AND (D_case.case_" & ConColName & " = " & ConId & ")"
                    Else
                        ExtraConSQL = " AND (D_case.case_" & ConColName & " = " & ConId & ")"
                    End If
                End If


                .Append(ExtraCaseSQL)
                .Append(ExtraConSQL)

                'If ConStatus = ConsultantStatus.Unassigned And ConId = 0 Then
                '    .Append(")")
                'End If

                Select Case DepartmentId
                    Case Is > 0
                        .Append(" AND (ProductType_DepartmentID = " & DepartmentId & ")")
                    Case PRODUCT_NONE
                        .Append(" AND (Product_ID In (0,28,41,188))")
                    Case PRODUCT_RETIRED
                        .Append(" AND ((Product_ID not In (0,28,41,188)) AND (Product_Type = 0))")

                End Select

                If ProductTypeID > 0 Then
                    .Append(" and ProductType_ID = " & ProductTypeID)
                End If

                .Append(" ORDER BY D_Case.Case_ID ")
                .Append(", case_dtsold, company, candidate, product, status, salescon, caseworker, coordinator, 'Date Created', Nationality, Residence, Destination, 'Entered by'")

                Return DB.GetSQLDataTable(.ToString)

            End With

        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing

        End Try
    End Function
    Public Shared Function AssignCaseworker(ByVal Cases_Data() As Long, ByVal ConType As ConsultantType, ByVal AssignToConId As Integer, ByVal MoveTasks As Boolean) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New CommonFuncs
        Dim creg As New CReg
        Dim SQL As New StringBuilder
        Dim bFail As Boolean = True
        Try
            DB.BeginTrans()

            With SQL

                .Append("Insert into D_CaseAudit ")
                .Append(" (CaseAudit_EnteredBy, CaseAudit_CaseID, CaseAudit_AuditType, CaseAudit_IDOld, CaseAudit_IDNew)")
                .Append(" Select " & GlobalCRM.CurrentConID & " as EB, Case_ID, ")

                Select Case ConType
                    Case ConsultantType.CaseWorker
                        .Append(" 3, Case_ConCWID, ")
                    Case ConsultantType.SalesCon
                        .Append(" 2, Case_ConSaleID, ")
                    Case ConsultantType.CaseCoordinator
                        .Append(" 7, Case_CoordinatorID, ")
                End Select

                .Append(AssignToConId)
                .Append(" From D_Case Where Case_ID in " & CommonFuncs.ArrayToCSV(Cases_Data))

            End With

            If Not DB.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            SQL = New StringBuilder

            With SQL

                .Append("Update D_Case set ")

                If ConType = ConsultantType.CaseWorker Then
                    .Append(" Case_ConCWID = ")
                ElseIf ConType = ConsultantType.SalesCon Then
                    .Append(" Case_ConSaleID = ")
                ElseIf ConType = ConsultantType.CaseCoordinator Then
                    .Append(" Case_CoordinatorID = ")
                End If

                .Append(AssignToConId & " ,Case_AllocatedBy = " & GlobalCRM.CurrentConID & ",Case_DTAllocated = ")

                .Append(" getdate() where case_ID in " & CommonFuncs.ArrayToCSV(Cases_Data))
            End With

            If Not DB.ExecuteSQL(SQL.ToString) Then
                DB.RollbackTrans()
                Exit Function
            End If

            SQL = New StringBuilder
            With SQL
                .Append("Update D_Case set ")
                If ConType = ConsultantType.CaseWorker Then
                    .Append(" Case_ConCWID = ")
                ElseIf ConType = ConsultantType.SalesCon Then
                    .Append(" Case_ConSaleID = ")
                ElseIf ConType = ConsultantType.CaseCoordinator Then
                    .Append(" Case_CoordinatorID = ")
                End If
                .Append(AssignToConId & " ,Case_AllocatedBy = " & GlobalCRM.CurrentConID & ",Case_DTAllocated = ")
                .Append(" getdate() where Case_MaincaseID in " & CommonFuncs.ArrayToCSV(Cases_Data))
            End With

            If Not DB.ExecuteSQL(SQL.ToString) Then
                DB.RollbackTrans()
                Exit Function
            End If


            'If entering Sales Con update Account manager where not already set or set to an inactive user.
            If ConType = ConsultantType.SalesCon Then

                SQL = New StringBuilder

                With SQL

                    .Append("Update D_Person set Person_AccountManager = " & AssignToConId)
                    .Append(" Where Person_AccountManager in (0,11)")
                    .Append(" OR Person_AccountManager not in (")
                    .Append(" Select Con_ID from L_Consultant where Con_Status = 1")
                    .Append(") ")
                    .Append(" AND Person_CompanyID <> 0")
                    .Append(" AND Person_ID IN (")
                    .Append(" Select Case_CandidateID from D_Case ")
                    .Append(" Where Case_ID in " & CommonFuncs.ArrayToCSV(Cases_Data))
                    .Append(" AND Case_CandidateID <> 0")
                    .Append(")")
                End With

                If Not DB.ExecuteSQL(SQL.ToString) Then
                    DB.RollbackTrans()
                    Exit Function
                Else
                    'send email - Logic to be written here
                    'get the case information and check whether they are web entered and has case status id as 5
                    'if they rae, then send automated email to the client
                    'this should be done for each case that is assigned
                    ' Check whether the products are all having the stagediagram id as 37 and send email only for them.
                    Dim i, j As Integer
                    Dim value As String
                    Dim DestCountryID As String
                    ', OriginCountryID As String
                    Dim sPersonData As DataTable
                    '-----------------------
                    'To check whether the cases are WWV cases.

                    'Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
                    'Dim WWVCountryList As String = CType(configurationAppSettings.GetValue("WWVCountryList", GetType(System.String)), String)
                    'Dim OriginWWVCountry As String = CType(configurationAppSettings.GetValue("OriginCountryWWVCheck", GetType(System.String)), String)
                    ' new code starts - to fetch values from database itself
                    ' Krishan Kumar k 
                    Dim WWVCountryList As String = String.Empty
                    Dim OriginWWVCountry As String = String.Empty
                    'SQL = New StringBuilder()
                    'With SQL
                    '    .Append("select customize_text,customize_Data from l_customization")
                    'End With
                    'Dim dataItems As DataTable = CommonDataFunctions.GetSQLDatatable(SQL.ToString)
                    'For Each currentRow As DataRow In dataItems.Rows
                    '    If currentRow.Item(0).ToString.Trim = "WWVCountryList" Then
                    '        WWVCountryList = currentRow.Item(1).ToString()
                    '    ElseIf currentRow.Item(0).ToString.Trim = "OriginCountryWWVCheck" Then
                    '        OriginWWVCountry = currentRow.Item(1).ToString()
                    '    End If
                    'Next


                    WWVCountryList = CommonDataFunctions.GetSQLScalar("select customize_text,customize_Data from l_customization where customize_text = 'WWVCountryList'")
                    WWVCountryList = CommonDataFunctions.GetSQLScalar("select customize_text,customize_Data from l_customization where customize_text = 'OriginCountryWWVCheck'")
                    ''new code ends 
                    Dim strCountryList() As String = Split(WWVCountryList, ",")
                    Dim blnWWV As Boolean = False
                    For i = 0 To Cases_Data.Length - 1
                        Dim locCaseID = Cases_Data(i)
                        blnWWV = False
                        sPersonData = DB.GetSQLDataTable("SELECT dbo.D_Person.Person_ResidentID, dbo.D_Person.Person_DestinationID FROM dbo.D_Case INNER JOIN  dbo.D_Person ON dbo.D_Case.Case_CandidateID = dbo.D_Person.Person_ID WHERE (dbo.D_Case.Case_ID = " & locCaseID & ")")
                        If Not sPersonData Is Nothing Then

                            If sPersonData.Rows.Count > 0 Then
                                'blnWWV = False
                                If OriginWWVCountry = sPersonData.Rows(0).Item(0).ToString Then
                                    DestCountryID = sPersonData.Rows(0).Item(1).ToString
                                    For j = 0 To strCountryList.Length - 1
                                        If strCountryList(j) = String.Empty Then
                                            blnWWV = False
                                            Exit For
                                        End If
                                        If DestCountryID = CInt(strCountryList(j)) Then
                                            blnWWV = True
                                            Exit For
                                        End If
                                    Next
                                    If blnWWV = True Then
                                        Dim myCase As Cases.CaseInfo = Cases.GetCaseInfo(locCaseID)
                                        Dim myPerson As People.PersonInfo = People.GetPerson(myCase.CandidateId)
                                        Admin.SendEmailToClientForWebLeads(myCase, myPerson, True)

                                    End If
                                End If
                            End If
                        End If
                    Next
                    '-----------------------
                    Dim strRegValue As String = creg.ReadValue(GlobalCRM.GLOBALVISA, GlobalCRM.CUST, GlobalCRM.OMITLIST, value)
                    Dim strCheckRegData() As String = Split(strRegValue, ",")
                    Dim blnOmit As Boolean
                    Dim templateid As Long = DB.GetSQLScalar("select customize_data from l_customization where customize_text like 'EmailTemplate_AllocateWebLeads'")
                    Dim ProductsForUSANoJob As String = DB.GetSQLScalar("select customize_data from l_customization where customize_text like 'USAWithoutJobProducts'")
                    If templateid > 0 Then
                        For i = 0 To Cases_Data.Length - 1
                            blnOmit = False
                            Dim DiagramId As Integer
                            Dim locCaseID = Cases_Data(i)
                            DiagramId = CommonDataFunctions.GblFunc_Get_Dataset(locCaseID, 1)
                            For j = 0 To strCheckRegData.Length - 1
                                If strCheckRegData(j) = String.Empty Then
                                    blnOmit = False
                                    Exit For
                                End If
                                If AssignToConId = CInt(strCheckRegData(j)) Then
                                    blnOmit = True
                                    Exit For
                                End If
                            Next
                            If blnOmit = False Then
                                If DiagramId = 37 Then
                                    Dim myCase As Cases.CaseInfo = Cases.GetCaseInfo(locCaseID)
                                    Dim myPerson As People.PersonInfo = People.GetPerson(myCase.CandidateId)
                                    'CommonFunc.SendEmailToClientForWebLeads(myCase, myPerson)

                                    Admin.SendEmailToClientForWebLeads(myCase, myPerson, templateid)
                                    If ProductsForUSANoJob.Contains(myCase.Productid) And CommonDataFunctions.HaveJobOffer(locCaseID) = 0 Then
                                        templateid = DB.GetSQLScalar("select customize_data from l_customization where customize_text like 'USAWithoutJobEmailTemplate'")
                                        Admin.SendEmailToClientForWebLeads(myCase, myPerson, templateid)
                                    End If


                                    'If CommonFunc.blnAmendDataBeforeEmail Then
                                    '    SQL = New StringBuilder

                                    '    With SQL

                                    '        .Append("Update D_Person set Person_AccountManager = " & 0)
                                    '        .Append(" Where Person_AccountManager in (0,11)")
                                    '        .Append(" OR Person_AccountManager not in (")
                                    '        .Append(" Select Con_ID from L_Consultant where Con_Status = 1")
                                    '        .Append(") ")
                                    '        .Append(" AND Person_CompanyID <> 0")
                                    '        .Append(" AND Person_ID IN (")
                                    '        .Append(" Select Case_CandidateID from D_Case ")
                                    '        .Append(" Where Case_ID = " & locCaseID)
                                    '        .Append(" AND Case_CandidateID <> 0")
                                    '        .Append(")")
                                    '    End With

                                    '    If Not DB.ExecuteSQL(SQL.ToString) Then
                                    '        DB.RollbackTrans()
                                    '        Exit Function
                                    '    End If
                                    'End If
                                End If
                            End If

                        Next
                    End If


                End If


            End If

            If MoveTasks Then

                '__ Move relevant tasks to new consultant __'

                'Update any scheduled but not completed tasks to new consultant tasks
                SQL = New StringBuilder
                With SQL
                    .Append("UPDATE D_Event")
                    .Append(" Set Event_ScheduledFor = " & AssignToConId)
                    .Append(" WHERE Event_ID IN (")
                    .Append(" SELECT D_Event.Event_ID")
                    .Append(" FROM D_Event ")
                    .Append(" WHERE (D_Event.Event_LinkID IN " & CommonFuncs.ArrayToCSV(Cases_Data) & ") And (D_Event.Event_LinkType = 1) And (D_Event.Event_DTScheduled > GetDate()) And (D_Event.Event_Category = 2)")

                    .Append(")")
                End With

                If Not DB.ExecuteSQL(SQL.ToString) Then
                    DB.RollbackTrans()
                    Exit Function
                End If

            End If

            SQL = New StringBuilder

            With SQL
                .Append("INSERT INTO D_Event ( Event_LinkID, Event_LinkType, Event_EnteredBy, Event_DTEntered, Event_Description, Event_Message, Event_Category)")
                .Append(" SELECT Case_ID, 1 as Linktype, " & GlobalCRM.CurrentConID & " as Con, '" & cmn.FormatDate(Now) & "' AS DT,")
                Select Case ConType
                    Case ConsultantType.CaseWorker
                        .Append("'Caseworker Assigned' AS Description, 'Caseworker set from Unassigned to " & CommonDataFunctions.GetConsultantName(AssignToConId) & " with Admin tool' AS Msg,1 as Cat")
                    Case ConsultantType.SalesCon
                        .Append("'Sales Consultant Assigned' AS Description, 'Sales Consultant set from Unassigned to " & CommonDataFunctions.GetConsultantName(AssignToConId) & " with Admin tool' AS Msg,1 as Cat")
                    Case ConsultantType.CaseCoordinator
                        .Append("'Case Coordinator Assigned' AS Description, 'Case Coordinator set from Unassigned to " & CommonDataFunctions.GetConsultantName(AssignToConId) & " with Admin tool' AS Msg,1 as Cat")
                End Select
                .Append(" FROM D_Case")
                .Append(" WHERE Case_ID In " & CommonFuncs.ArrayToCSV(Cases_Data))
            End With

            If Not DB.ExecuteSQL(SQL.ToString) Then
                DB.RollbackTrans()
                Exit Function
            End If

            'DB.CommitTrans()
            bFail = False

            Return True

            'Call SQL_UnassignedCW()
            'Call SQL_Selected()
        Catch ex As Exception
            'MsgBox("There was an error assigning the case worker." & vbCrLf & "Please retry then ask support if no success.", MsgBoxStyle.Exclamation, "Assign Case Worker")
            If DB.IsTransactionOpen = True Then
                DB.RollbackTrans()
            End If
            CommonFuncs.ErrorHandler(ex, "Admin.AssignCaseWorker", True)
        Finally
            If bFail = True Then
                CommonFuncs.DisplayMessage("There was an error assigning the Sales Consultant." & vbCrLf & "Please retry then ask support if no success.", CommonDialog.CustomDialogIcons.Information, "Assign Sales Consultant")
            End If
        End Try
    End Function
    Public Shared Sub SendEmailToClientForWebLeads(ByVal mycaseInfo As Cases.CaseInfo, ByVal myPerson As People.PersonInfo, ByVal templateid As Long)
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Try


            'Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
            'Dim TemplateID As Long = CType(configurationAppSettings.GetValue("EmailTemplate_AllocateWebLeads", GetType(System.Int16)), Integer)
            'Dim TemplateID As Long = 409
            'If blnWWV = True Then
            'TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_WWVEmail", GetType(System.Int16)), Integer)
            '            End If

            If TemplateID <> 0 Then
                Dim EmailFrom As String
                Dim EmailTo As String
                Dim sqlbldr As New StringBuilder



                Dim DataType As Integer
                Dim DataID As Long
                If GlobalCRM.CurrentCompanyID = 0 Then
                    DataType = 1
                    DataID = mycaseInfo.CaseId
                    sqlbldr = New StringBuilder
                    sqlbldr.Append("Select EmailAddress_Address from D_EmailAddress where EmailAddress_personID = " & myPerson.PersonId)
                    EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    If EmailTo Is Nothing Then
                        'MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
                        Exit Sub
                    End If
                    If EmailTo.Trim <> "" Then
                        If CommonFuncs.isEmailValid(EmailTo) = False Then
                            Exit Sub
                        End If

                    End If
                Else
                    DataType = 3
                    DataID = GlobalCRM.CurrentCompanyID
                    'Fetch the company's contact and send email to associated ocntact and not the candidate
                    sqlbldr = New StringBuilder
                    sqlbldr.Append("select EmailAddress_Address from D_EmailAddress where EmailAddress_personID in (select case_contactid from d_case where case_candidateid = " & myPerson.PersonId & ")")
                    EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    If EmailTo Is Nothing Then
                        'MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
                        Exit Sub
                    End If
                    If CommonFuncs.isEmailValid(EmailTo) = False Then
                        Exit Sub
                    End If
                End If
                Dim dt As DataTable

                Dim myTemplate As CommonDataFunctions.EmailTemplateInfo
                myTemplate = CommonDataFunctions.GetEmailTemplate(TemplateID)
                'Dim StrSQL As String

                Dim StrSQLBookmarks As String = String.Empty
                Dim intCount As Integer = 1
                Dim txtfile As String = System.IO.Path.GetTempPath & "CRMTemp\sample.txt"
                Directory.CreateDirectory(System.IO.Path.GetTempPath & "CRMTemp")

                Dim oWrite As System.IO.StreamWriter
                oWrite = File.CreateText(System.IO.Path.GetTempPath & "CRMTemp\sample.txt")
                oWrite.Write(myTemplate.Body)
                oWrite.Close()

                Dim oRead As System.IO.StreamReader
                Dim LineIn As String
                Dim strNew() As String
                Dim strnew1() As String
                Dim i As Integer
                Dim c As New Collection
                Dim cData As Collection = Nothing
                Dim cIndex As New Collection
                Dim bln As Boolean
                oRead = IO.File.OpenText(txtfile)
                While oRead.Peek <> -1
                    LineIn = oRead.ReadLine()
                    strNew = Split(LineIn, "[")
                    For i = 0 To strNew.Length - 1
                        strnew1 = Split(strNew(i).ToString, "]")
                        If strnew1.Length > 1 Then
                            'StrSQLBookmarks = strnew1(0).ToString
                            StrSQLBookmarks = StrSQLBookmarks & "'" & strnew1(0).ToString & "',"
                            If cIndex.Contains(strnew1(0).ToString) = False Then
                                cIndex.Add(i, strnew1(0).ToString)
                                bln = True
                            End If
                            If bln Then
                                c.Add(strnew1(0).ToString)
                            End If
                            bln = False
                        End If
                    Next
                End While
                oRead.Close()

                StrSQLBookmarks = Left(StrSQLBookmarks, (Len(StrSQLBookmarks) - 1))
                StrSQLBookmarks = "(" & StrSQLBookmarks & ")"

                Dim sql1 As New StringBuilder
                With sql1
                    .Append("SELECT BookMark_Text as BMName")
                    .Append(" FROM I_SL_Bookmarks")
                    .Append(" WHERE Bookmark_Text in " & StrSQLBookmarks & "")
                    '.Append(" AND Bookmark_VersionID <= " & 1)
                    .Append(" GROUP BY BookMark_Text")
                End With
                dt = db.GetSQLDataTable(sql1.ToString)
                If Not dt Is Nothing Then
                    If dt.Rows.Count > 0 Then
                        Dim StrBMData As String = String.Empty
                        cData = New Collection
                        For i = 0 To dt.Rows.Count - 1
                            If dt.Rows(i).Item("BMName") <> "" Then
                                If GlobalCRM.CurrentCompanyID <> 0 Then
                                    If dt.Rows(i).Item("BMName") = "CandidateDearName" Then
                                        StrBMData = Letters.Gblfunc_Get_BookmarkData("ContactDearName", DataID, DataType)
                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                    Else
                                        DataType = 1
                                        DataID = mycaseInfo.CaseId
                                        StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                    End If
                                Else
                                    If dt.Rows(i).Item("BMName") = "CandidateDearName" Then
                                        StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)

                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                    Else
                                        If dt.Rows(i).Item("BMName") = "ConCaseSuperEMail" Then
                                            StrBMData = db.GetSQLScalar("select con_email from l_consultant where con_id = " & mycaseInfo.SalesConid)
                                        End If
                                        If dt.Rows(i).Item("BMName") = "ConCaseSuperDDI" Then
                                            StrBMData = db.GetSQLScalar("select con_DDI from l_consultant where con_id = " & mycaseInfo.SalesConid)
                                        End If
                                        If dt.Rows(i).Item("BMName") = "ConCaseSuperName" Then
                                            StrBMData = db.GetSQLScalar("select con_name from l_consultant where con_id = " & mycaseInfo.SalesConid)
                                        End If
                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))

                                        'Exit For
                                    End If
                                End If
                            End If
                            If StrBMData = "EmailError_IT_Contact" Then
                                System.IO.File.Delete(txtfile)
                                Directory.Delete(System.IO.Path.GetTempPath & "CRMTemp\")
                                Exit Sub
                            End If
                        Next
                    End If
                End If
                oRead = IO.File.OpenText(txtfile)
                Dim strRep As String = oRead.ReadToEnd
                oRead.Close()
                sqlbldr = New StringBuilder
                sqlbldr.Append("Select Con_EMail from L_Consultant where Con_ID = " & mycaseInfo.SalesConid)
                EmailFrom = db.GetSQLScalar(sqlbldr.ToString)

                If EmailFrom Is Nothing Then
                    CommonFuncs.DisplayMessage("Your email id is not updated in the database." & vbCrLf & "Please contact IT", CommonDialog.CustomDialogIcons.Information, "Email Not sent")
                    Exit Sub

                End If
                Dim OpToEmail As String
                For i = 1 To cData.Count
                    If OpToEmail = String.Empty Then
                        OpToEmail = strRep.Replace("[" & c.Item(i) & "]", cData.Item(c.Item(i)))
                    Else
                        OpToEmail = OpToEmail.Replace("[" & c.Item(i) & "]", cData.Item(c.Item(i)))
                    End If
                Next
                'Dim subj As String = "Global Visas Case Update"
                Dim Signature As String = CommonDataFunctions.GblFunc_Get_Sigfile(mycaseInfo.SalesConid)
                OpToEmail = OpToEmail & vbCrLf & Signature

                EMAILBODYSTRING = OpToEmail
                EMAILSUBJECTSTRING = GlobalCRM.subj
                EMAILFROMSTRING = EmailFrom

                EMAILTOSTRING = EmailTo
                'frmEmailCaseNavigation.btnBack.Enabled = False
                'frmEmailCaseNavigation.btnBack.Visible = False
                'frmEmailCaseNavigation.ShowDialog()
                'If blnAmendDataBeforeEmail Then
                '            Exit Sub
                'Else
                Dim Success As Integer = Letters.GblFunc_Send_MailEngine(EMAILTOSTRING, EMAILFROMSTRING, EMAILSUBJECTSTRING, EMAILBODYSTRING, , EMAILBCCSTRING)
                EMAILSUBJECTSTRING = String.Empty
                EMAILBODYSTRING = String.Empty
                EMAILBCCSTRING = String.Empty
                Dim Msg As New StringBuilder
                With Msg
                    .Append("Email Sent" & vbCrLf & vbCrLf & "To: " & EmailTo)

                    .Append(vbCrLf & vbCrLf & "Sent via mail engine")
                    .Append(vbCrLf & "Subject: " & GlobalCRM.subj)

                    .Append(vbCrLf & "Message: " & vbCrLf & vbCrLf & OpToEmail)
                End With

                tsk.GblFunc_Add_Event("Send Email", Msg.ToString, TasksEvents.EventStatus.SingleEvent, mycaseInfo.CaseId, TasksEvents.EventTypes.EventCase, , , , , 1)
                If Success = 1 Then
                    'MsgBox("Email has been sent to the client successfully." & vbCrLf & "Please ensure with your client whether the email address is correct", MsgBoxStyle.Information, "Success")
                End If
                System.IO.File.Delete(txtfile)
                Directory.Delete(System.IO.Path.GetTempPath & "CRMTemp\")
                'End If
            End If

            'Catch ex As Exception
            'Return False
            'End Try
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "CommonFunc.SendEmailToClientForWebLeads", True)
        Finally
            db = Nothing
            tsk = Nothing
        End Try
    End Sub
    Public Shared Function DeleteLead(ByVal CaseId As Long) As Boolean

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim sql As StringBuilder
        Try
            Sql = New StringBuilder

            Sql.Append("update d_case set case_statusid = 1030, case_dtstatus = getdate() where case_id = " & CaseId)

            If Not db.ExecuteSQL(Sql.ToString) Then
                Exit Function
            End If

            tsk.GblFunc_Add_Event("Cancel", "New lead cancelled without being assigned", TasksEvents.EventStatus.SingleEvent, CaseId, 1)

            Return True

        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "CommonFunc.SendEmailToClientForWebLeads", True)
        Finally
            db = Nothing
            tsk = Nothing
            sql = Nothing
        End Try
    End Function
    Public Shared Function GetAllProductTypes(Optional ByVal Branchid As Integer = 0) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim sql As StringBuilder
        Dim dt As DataTable
        Try
            sql = New StringBuilder
            With sql
                .Append(" SELECT     ProductType_ID, ProductType_Name")
                .Append(" FROM dbo.L_ProductTypes")
                If Branchid > 0 Then
                    .Append(" WHERE(ProductType_BranchID = " & Branchid & ")")
                End If
                .Append(" ORDER BY ProductType_Name")
            End With
            db.GetSQLDataTable(sql.ToString)
            dt = db.GetSQLDataTable(sql.ToString)
            If Not dt Is Nothing Then
                If dt.Rows.Count > 0 Then
                    Return dt
                End If
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            tsk = Nothing
            sql = Nothing
        End Try
    End Function

    Public Shared Function Gblfunc_Add_CaseAudit(ByVal CaseID As Long, ByVal AuditType As Integer, ByVal ValueFrom As Long, ByVal ValueTo As Long) As Integer
        Try

            'Audit Types

            ' 0 = not valid
            ' 1 = Product
            ' 2 = Sales Con
            ' 3 = Caseworker
            ' 4 = Candidate
            ' 5 = Contact
            ' 6 = Status

            Dim StrSQL As String, Rst As DataTable

            StrSQL = "Insert into D_CaseAudit"
            StrSQL = StrSQL & " (CaseAudit_EnteredBy, CaseAudit_CaseID, CaseAudit_AuditType, CaseAudit_IDOld, CaseAudit_IDNew)"
            StrSQL = StrSQL & " Values("
            StrSQL = StrSQL & GlobalCRM.CurrentConID & ", "
            StrSQL = StrSQL & CaseID & ", "
            StrSQL = StrSQL & AuditType & ", "
            StrSQL = StrSQL & ValueFrom & ", "
            StrSQL = StrSQL & ValueTo & ")"

            If CommonDataFunctions.ExecuteSQL(StrSQL) = False Then Exit Function

            Return 1
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "Admin-Gblfunc_Add_CaseAudit")
        End Try

    End Function
    Public Shared Function GetcaseStages(ByVal diagramid) As DataTable
        Dim sql As New StringBuilder
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try

        
            With sql
                .Append(" SELECT DISTINCT dbo.L_StageLinks.StageLink_FromID, dbo.L_CaseStages.CaseStage_Title")
                .Append(" FROM         dbo.L_StageLinks INNER JOIN")
                .Append(" dbo.L_CaseStages ON dbo.L_StageLinks.StageLink_FromID = dbo.L_CaseStages.CaseStage_Number")
                .Append(" WHERE (dbo.L_StageLinks.StageLink_DiagramID = " & diagramid & ")")

            End With
            Return db.GetSQLDataTable(sql.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            sql = Nothing
        End Try
    End Function
    Public Shared Function GetcaseStages_To(ByVal fromid, ByVal diagramid) As DataTable
        Dim sql As New StringBuilder
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            With sql
                .Append(" SELECT DISTINCT dbo.L_StageLinks.StageLink_ToID, dbo.L_CaseStages.CaseStage_Title")
                .Append(" FROM         dbo.L_StageLinks INNER JOIN")
                .Append(" dbo.L_CaseStages ON dbo.L_StageLinks.StageLink_ToID = dbo.L_CaseStages.CaseStage_Number")
                .Append(" WHERE     (dbo.L_StageLinks.StageLink_DiagramID = " & diagramid & ") AND (dbo.L_StageLinks.StageLink_FromID = " & fromid & ")")
            End With
            Return db.GetSQLDataTable(sql.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            sql = Nothing
        End Try
    End Function

    Public Shared Function GetEmailTemplateforcasestage(ByVal fromid As Long, ByVal toid As Long, ByVal diagramid As Long) As Long
        Dim sql As New StringBuilder
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            With sql
                .Append(" SELECT DISTINCT StageLink_EmailTemplate")
                .Append(" FROM dbo.L_StageLinks")
                .Append(" WHERE     (StageLink_FromID = " & fromid & ") AND (StageLink_ToID = " & toid & ") AND (StageLink_DiagramID = " & diagramid & ")")
            End With
            Return db.GetSQLScalar(sql.ToString)
        Catch ex As Exception
            Return 0
        Finally
            sql = Nothing
        End Try
    End Function
    Public Shared Function GetTaskTemplateforcasestage(ByVal fromid As Long, ByVal toid As Long, ByVal diagramid As Long) As Long
        Dim sql As New StringBuilder
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            With sql
                .Append(" SELECT mapping .TaskTemplate_ID FROM L_StageLinksTaskTemplateMapping mapping join ")
                .Append(" dbo.L_StageLinks links on ( mapping.StageLink_FromId =links .StageLink_FromID  and ")
                .Append("  mapping .StageLink_ToID = links.StageLink_ToID  ) where links.StageLink_FromID=" & fromid)
                .Append(" and links.StageLink_ToID=" & toid & " and links.StageLink_DiagramID = " & diagramid)
            End With
            Return db.GetSQLScalar(sql.ToString)
        Catch ex As Exception
            Return 0
        Finally
            sql = Nothing
        End Try
        'Try
        '    With sql
        '        .Append(" SELECT DISTINCT StageLink_TaskTemplete")
        '        .Append(" FROM dbo.L_StageLinks")
        '        .Append(" WHERE     (StageLink_FromID = " & fromid & ") AND (StageLink_ToID = " & toid & ") AND (StageLink_DiagramID = " & diagramid & ")")
        '    End With
        '    Return db.GetSQLScalar(sql.ToString)
        'Catch ex As Exception
        '    Return 0
        'Finally
        '    sql = Nothing
        'End Try
    End Function

    Public Shared Function GetTaskTemplatesForCaseStage(ByVal fromid As Long, ByVal toid As Long) As DataTable
        Dim sql As New StringBuilder
        Dim dt As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            With sql
                .Append(" SELECT  stages.CaseStage_Title as 'From' ,stages1.CaseStage_Title as 'To' ,templates.TaskTemplate_Message as 'Task Template' ")
                .Append(" ,mapping.[TaskTemplate_WorkingDays] as 'Working Days', templates.TaskTemplate_ID as TemplateId , links .StageLink_FromID as 'stagefrom' , ")
                .Append(" links.StageLink_ToID as 'stageTo' FROM [L_StageLinks] links join [L_StageLinksTaskTemplateMapping] mapping on ")
                .Append(" (links.StageLink_FromID = mapping .StageLink_FromId and links .StageLink_ToID = mapping .StageLink_ToID ) join  ")
                .Append(" D_TaskTemplates templates on mapping .TaskTemplate_ID = templates .TaskTemplate_ID ")
                .Append(" join L_CaseStages stages on links.StageLink_FromID = stages .CaseStage_ID join L_CaseStages stages1 ")
                .Append(" on links.StageLink_ToID =stages1.CaseStage_ID ")
                If (fromid > 0 And toid > 0) Then
                    .Append("  where links.StageLink_FromID = " & fromid & " And links.StageLink_ToID = " & toid)
                End If
            End With
            dt = db.GetSQLDataTable(sql.ToString)
        Catch ex As Exception
            dt = Nothing
        Finally
            sql = Nothing
        End Try
        Return dt
    End Function

    Public Shared Function GetTaskInfoForManageAutomation(ByVal caseStageFromId As Integer, ByVal caseStageToId As Integer) As TaskTemplateInfo
        Dim sql As New StringBuilder
        Dim dt As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim taskTemplateInfo As New TaskTemplateInfo
        Try
            With sql
                .Append(" SELECT  TaskTemplates.[TaskTemplate_ID],TaskTemplates.[TaskTemplate_Name],TaskTemplates.[TaskTemplate_Type],")
                .Append(" TaskTemplates.[TaskTemplate_Message],mapping. [TaskTemplate_WorkingDays] FROM L_StageLinksTaskTemplateMapping mapping ")
                .Append(" join [L_StageLinks] as SL on ( mapping.StageLink_FromId=sl.StageLink_FromID and mapping.StageLink_ToID =sl.StageLink_ToID ) ")
                .Append(" join [D_TaskTemplates] as TaskTemplates on ")
                .Append(" TaskTemplates.TaskTemplate_ID =  mapping.TaskTemplate_ID where ")
                .Append(" SL.StageLink_FromID =" & caseStageFromId & " And SL.StageLink_ToID = " & caseStageToId)
            End With
            'With sql
            '    .Append(" SELECT  [TaskTemplate_ID],[TaskTemplate_Name],[TaskTemplate_Type],[TaskTemplate_Message] ")
            '    .Append(",[TaskTemplate_WorkingDays] FROM [D_TaskTemplates] as TaskTemplates join [L_StageLinks] as SL on ")
            '    .Append("  TaskTemplates.TaskTemplate_ID = SL.StageLink_TaskTemplete where SL.StageLink_FromID = '" & caseStageFromId & "' and SL.StageLink_ToID = '" & caseStageToId & "'")
            'End With
            dt = db.GetSQLDataTable(sql.ToString())
            If (dt.Rows.Count > 0) Then
                taskTemplateInfo.TemplateId = dt.Rows(0).Item("TaskTemplate_ID").ToString()
                taskTemplateInfo.Message = dt.Rows(0).Item("TaskTemplate_Message").ToString()
                taskTemplateInfo.TemplateName = dt.Rows(0).Item("TaskTemplate_Name").ToString()
                taskTemplateInfo.TemplateType = dt.Rows(0).Item("TaskTemplate_Type").ToString()
                taskTemplateInfo.TemplateWorkingDays = Convert.ToInt32(dt.Rows(0).Item("TaskTemplate_WorkingDays").ToString())
            End If
            
        Catch ex As Exception
            Return Nothing
        Finally
            sql = Nothing
        End Try
        Return taskTemplateInfo
    End Function

    Public Shared Function GetTaskInfosForManageAutomation(ByVal caseStageFromId As Integer, ByVal caseStageToId As Integer) As List(Of TaskTemplateInfo)
        Dim sql As New StringBuilder
        Dim dt As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim taskTemplateInfo As New List(Of TaskTemplateInfo)
        Try
            With sql
                .Append(" SELECT  TaskTemplates.[TaskTemplate_ID],TaskTemplates.[TaskTemplate_Name],TaskTemplates.[TaskTemplate_Type],")
                .Append(" TaskTemplates.[TaskTemplate_Message],mapping. [TaskTemplate_WorkingDays] FROM L_StageLinksTaskTemplateMapping mapping ")
                .Append(" join [L_StageLinks] as SL on ( mapping.StageLink_FromId=sl.StageLink_FromID and mapping.StageLink_ToID =sl.StageLink_ToID ) ")
                .Append(" join [D_TaskTemplates] as TaskTemplates on ")
                .Append(" TaskTemplates.TaskTemplate_ID =  mapping.TaskTemplate_ID where ")
                .Append(" SL.StageLink_FromID =" & caseStageFromId & " And SL.StageLink_ToID = " & caseStageToId)
            End With
            'With sql
            '    .Append(" SELECT  [TaskTemplate_ID],[TaskTemplate_Name],[TaskTemplate_Type],[TaskTemplate_Message] ")
            '    .Append(",[TaskTemplate_WorkingDays] FROM [D_TaskTemplates] as TaskTemplates join [L_StageLinks] as SL on ")
            '    .Append("  TaskTemplates.TaskTemplate_ID = SL.StageLink_TaskTemplete where SL.StageLink_FromID = '" & caseStageFromId & "' and SL.StageLink_ToID = '" & caseStageToId & "'")
            'End With
            dt = db.GetSQLDataTable(sql.ToString())
            Dim taskTemplate As New TaskTemplateInfo()
            For Each dr As DataRow In dt.Rows
                taskTemplate.TemplateId = dr.Item("TaskTemplate_ID").ToString()
                taskTemplate.Message = dr.Item("TaskTemplate_Message").ToString()
                taskTemplate.TemplateName = dr.Item("TaskTemplate_Name").ToString()
                taskTemplate.TemplateType = dr.Item("TaskTemplate_Type").ToString()
                taskTemplate.TemplateWorkingDays = Convert.ToInt32(dr.Item("TaskTemplate_WorkingDays").ToString())
                taskTemplateInfo.Add(taskTemplate)
            Next

        Catch ex As Exception
            Return Nothing
        Finally
            sql = Nothing
        End Try
        Return taskTemplateInfo
    End Function

    Public Shared Sub BookTheRelevantTask(ByVal fromId As Integer, ByVal toId As Integer)
        Dim test As Singleton = Singleton.Instance
        Dim taskEvents As New Global_Visas_Business_Layer.TasksEvents()
        Dim taskTemplatesInfo As List(Of Admin.TaskTemplateInfo) = Admin.GetTaskInfosForManageAutomation(fromId, toId)
        For Each taskTemplate As TaskTemplateInfo In taskTemplatesInfo
            Dim taskDate As DateTime = DateTime.Now.AddDays(taskTemplate.TemplateWorkingDays)
            taskEvents.GblFunc_Add_Event(taskTemplate.TemplateType, taskTemplate.Message, Global_Visas_Business_Layer.TasksEvents.EventStatus.Task, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, False, 0, test.CurrentConId, taskDate, 1)
        Next
    End Sub

    Public Shared Function GetEmailTemplateBodyFromID(ByVal Id As Long) As String
        Dim sql As New StringBuilder
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            With sql
                .Append(" Select EMailTemplate_Body")
                .Append(" FROM dbo.D_EmailTemplates")
                .Append(" WHERE (EMailTemplate_ID = " & Id & ")")
            End With
            Return db.GetSQLScalar(sql.ToString)
        Catch ex As Exception
            Return 0
        Finally
            sql = Nothing
        End Try
    End Function
    Public Shared Function GetTaskTemplateMessageFromID(ByVal Id As Long) As TaskTemplateInfo
        Dim sql As New StringBuilder
        Dim temlateInfo As TaskTemplateInfo = Nothing
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            With sql
                .Append(" Select [TaskTemplate_Message], [TaskTemplate_WorkingDays]")
                .Append(" FROM dbo.D_TaskTemplates")
                .Append(" WHERE (TaskTemplate_ID = " & Id & ")")
            End With
            Dim dt As DataTable = db.GetSQLDataTable(sql.ToString)
            If (dt.Rows.Count > 0) Then
                temlateInfo = New TaskTemplateInfo()
                temlateInfo.TemplateWorkingDays = Convert.ToInt32(dt.Rows(0).ItemArray(1).ToString())
                temlateInfo.Message = dt.Rows(0).ItemArray(0).ToString()
            End If
        Catch ex As Exception

        Finally
            sql = Nothing
        End Try
        Return temlateInfo
    End Function
    Public Shared Function GetEmailtemplateslist() As DataTable
        Dim sql As New StringBuilder
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            With sql
                .Append(" SELECT DISTINCT EMailTemplate_ID, EMailTemplate_Name")
                .Append(" FROM dbo.D_EmailTemplates")
            End With
            Return db.GetSQLDataTable(sql.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            sql = Nothing
        End Try
    End Function
    Public Shared Function UpdateEmailAutomation(ByVal fromid As Long, ByVal toid As Long, ByVal diagramid As Long, ByVal templateid As Long) As Boolean
        Dim sql As New StringBuilder
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            With sql
                .Append(" update L_StageLinks set StageLink_EmailTemplate=" & templateid)
                .Append(" where StageLink_ToID = " & toid & " and StageLink_FromId = ")
                .Append(fromid & " and StageLink_DiagramID = " & diagramid)

            End With
            Return db.ExecuteSQL(sql.ToString)
        Catch ex As Exception
            Return False
        Finally
            sql = Nothing
        End Try
    End Function

    Public Shared Function UpdateTaskAutomation(ByVal fromid As Long, ByVal toid As Long, ByVal diagramid As Long, ByVal templateid As Long, ByVal workingDays As Integer) As Boolean
        Dim sql As New StringBuilder
        Dim result As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            db.BeginTrans()
            With sql
                .Append("SELECT  * FROM L_StageLinksTaskTemplateMapping mapping where ")
                .Append(" mapping.StageLink_FromID = " & fromid & " And mapping.StageLink_ToID =" & toid & " and mapping.TaskTemplate_ID=" & templateid)
            End With
            Dim dt As DataTable = db.GetSQLDataTable(sql.ToString())
            Dim count As Integer = dt.Rows.Count
            Dim insertUpdateFlag As Boolean = False
            If (count = 1) Then
                insertUpdateFlag = True
            End If
            sql = New StringBuilder()
            If (insertUpdateFlag = False) Then
                With sql
                    .Append(" INSERT INTO [L_StageLinksTaskTemplateMapping] ([StageLink_FromId] ")
                    .Append(" ,[StageLink_ToID] ,[TaskTemplate_ID] ,[TaskTemplate_WorkingDays]) ")
                    .Append(" VALUES( " & fromid & "," & toid & "," & templateid & "," & workingDays & ")")
                End With
                result = db.ExecuteSQL(sql.ToString)
            Else
                'Update
                With sql
                    .Append(" update L_StageLinksTaskTemplateMapping set TaskTemplate_WorkingDays=" & workingDays)
                    .Append(" where StageLink_ToID = '" & toid & "' and StageLink_FromId = '")
                    .Append(fromid & "' and TaskTemplate_ID = '" & templateid & "'")
                End With
                result = db.ExecuteSQL(sql.ToString)
            End If
            
            If (result) Then
                db.CommitTrans()
            Else
                db.RollbackTrans()
            End If

        Catch ex As Exception
            result = False
            db.RollbackTrans()
        Finally
            db = Nothing
            sql = Nothing
        End Try
        Return result
    End Function


End Class
